{% if ( max_memory_size_mb | int ) <= (ansible_memtotal_mb | int ) %}
    {% set ansible_memtotal_mb = max_memory_size_mb %}
{% endif %}
[mysql]
auto-rehash
socket                              =/tmp/mysql-{{mysql_port}}.sock           #   /tmp/mysql.sock


[mysqld]
####: for global
user                                ={{mysql_user}}
server_id                           ={{ 4096 |random(1) }}                           # 1 
basedir                             ={{mysql_base_dir}}              # /usr/local/mysql/
datadir                             ={{mysql_data_dir}}      # /usr/local/mysql/data/
max_prepared_stmt_count             =1048576                        # 16382
open_files_limit                    =102000                         # 65536
port                                ={{mysql_port}}                           # 3306
mysqlx_port                         ={{mysql_xport}}                          # 33060
admin_port                          ={{mysql_admin_port}}                          # 33062
socket                              =/tmp/mysql-{{mysql_port}}.sock           # /tmp/mysql.sock
skip_name_resolve                   =1                              #   0
super_read_only                     =OFF                            # OFF
sql_require_primary_key             =OFF                            # OFF
cte_max_recursion_depth             =1000                           # 1000
log_timestamps                      =system                         # UTC
lower_case_table_names              =1                              # 0
auto_increment_increment            =1                              # 1
auto_increment_offset               =1                              # 1
lock_wait_timeout                   =31536000                       # !
event_scheduler                     =OFF                            # ON
auto_generate_certs                 =ON                             # ON
big_tables                          =OFF                            # OFF
join_buffer_size                    =256k                           # 0.25M
activate_all_roles_on_login         =ON                             # OFF
end_markers_in_json                 =OFF                            # OFF
tmpdir                              =/tmp/
{% if (ansible_memtotal_mb | int)   <= 4096 %}
thread_cache_size                   =9                              #   9
max_connections                     =151                            # 151
{% elif (ansible_memtotal_mb | int) <= 8192 %}
thread_cache_size                   =32                             #   9
max_connections                     =255                            # 151
{% elif (ansible_memtotal_mb | int) <= 16384 %}
thread_cache_size                   =64                             #   9
max_connections                     =512                            # 151
{% elif (ansible_memtotal_mb | int) <= 32768 %}
thread_cache_size                   =128                            #   9
max_connections                     =1000                           # 151
{% else %}
thread_cache_size                   =128                            #   9
max_connections                     =1024                           # 151
{% endif %}
autocommit                          =ON                             # ON
sort_buffer_size                    =256k                           # 262144(256k)


####: for table cache
{% if (ansible_memtotal_mb | int)   <= 4096 %}
table_open_cache                    =4000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =16                             #   16
{% elif (ansible_memtotal_mb | int) <= 8192 %}
table_open_cache                    =8000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =16                             #   16
{% elif (ansible_memtotal_mb | int) <= 16384 %}
table_open_cache                    =16000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% elif (ansible_memtotal_mb | int) <= 32768 %}
table_open_cache                    =20000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% else %}
table_open_cache                    =24000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% endif %}


####: for net
max_allowed_packet                  =1G                             # 64M
bind_address                        =*                              # *
connect_timeout                     =10                             # 10
interactive_timeout                 =28800                          # 28800
net_read_timeout                    =30                             # 30
net_retry_count                     =10                             # 10
net_write_timeout                   =60                             # 60
net_buffer_length                   =32k                            # 16384(16k)


####: for logs
log_output                          =FILE                           # FILE
##  -- general log
general_log                         =OFF                            # OFF
general_log_file                    =general.log                    # $(hotname).log
##  -- error log
log_error                           =err.log                        # stderr
log_statements_unsafe_for_binlog    =ON                             # ! for error 1592
##  -- slow log
long_query_time                          =2.0                       # 10.000000
log_queries_not_using_indexes            =OFF                       # OFF
log_slow_admin_statements                =OFF                       # OFF
log_slow_slave_statements                =OFF                       # OFF
slow_query_log                           =ON                        # OFF
slow_query_log_file                      =slow.log                  # slow.log
                  

####: for binlog
log_bin                                       =mysql-bin
binlog_checksum                               =none                          # CRC32
log_bin_trust_function_creators               =ON                            # OFF
binlog_direct_non_transactional_updates       =OFF                           # OFF
binlog_expire_logs_seconds                    =604800                        # 2592000(30days) | 604800(7days)
binlog_error_action                           =ABORT_SERVER                  # ABORT_SERVER | IGNORE_ERROR
binlog_format                                 =ROW                           # ROW | STATEMENT | MIXED
max_binlog_stmt_cache_size                    =1G                            # 18446744073709547520
max_binlog_cache_size                         =1G                            # 18446744073709547520(1G)
max_binlog_size                               =1G
binlog_order_commits                          =ON                            # ON
binlog_row_image                              =FULL                          # FULL | MINIMAL | NOBLOB
binlog_row_metadata                           =MINIMAL                       # MINIMAL | FULL
binlog_rows_query_log_events                  =ON                            # OFF
log_slave_updates                             =ON                            # ON
binlog_stmt_cache_size                        =32k                           # 32768(32k)
sync_binlog                                   =1                             # 1 | 0 | N
binlog_group_commit_sync_delay                =500                           # 0
binlog_group_commit_sync_no_delay_count       =10                            # 0
binlog_cache_size                             =64k                           # 32768(32k)
binlog_transaction_dependency_history_size    =25000                         # 25000
binlog_transaction_dependency_tracking        =WRITESET                      # COMMIT_ORDER | WRITESET | WRITESET_SESSION


####: for replication
## -- replication master
master_info_repository                        =table                          # TABLE | FILE
sync_master_info                              =10000                          # 10000           #
rpl_semi_sync_master_timeout                  =1000                           # 1000(1 second)
plugin_load_add                               =semisync_master.so 
rpl_semi_sync_master_enabled                  =1                              # 0
## -- replication slave
relay_log_info_repository                     =table                          #	file
skip_slave_start                              =0                              # 0
slave_parallel_type                           =logical_clock                  # database | LOGICAL_CLOCK
slave_parallel_workers                        ={{[ansible_processor_count * 4 ,32] | min}}                               # 0
slave_max_allowed_packet                      =1073741824                     # 1073741824 
slave_load_tmpdir                             =/tmp                           # /tmp/
sync_relay_log                                =10000                          # 10000
sync_relay_log_info                           =10000                          # 10000
plugin_load_add                               =semisync_slave.so              #
rpl_semi_sync_slave_enabled                   =1                              # 0
slave-preserve-commit-order                   =ON                             #

####: for gtid
binlog_gtid_simple_recovery                   =ON                            # ON
enforce_gtid_consistency                      =ON                            # ON
gtid_executed_compression_period              =1000                          # 1000
gtid_mode                                     =ON                            # OFF


####: for storage engine
default_storage_engine                        =innodb                        # InnoDB
default_tmp_storage_engine                    =innodb                        # InnoDB
internal_tmp_mem_storage_engine               =TempTable                     # TempTable


####: for innodb
## disk I/O and file space management
innodb_data_home_dir                =./                             # ./
innodb_data_file_path               =ibdata1:64M:autoextend         # ibdata1:12M:autoextend
innodb_page_size                    =16k                            # 16384(16k)
innodb_default_row_format           =dynamic                        # dynamic | compact | redundant
innodb_log_group_home_dir           =./                             # ./
innodb_log_files_in_group           =8                              # 2
{% if (ansible_memtotal_mb | int)   <= 4096 %}
innodb_log_file_size                =128M                           # 50331648(48M)
{% else %}
innodb_log_file_size                =256M                           # 50331648(48M)
{% endif %}

{% if (ansible_memtotal_mb | int)   <= 1024 %}
innodb_log_buffer_size              =64M                            # 16777216(16M)
{% elif (ansible_memtotal_mb | int)   <= 2048 %}
innodb_log_buffer_size              =128M                           # 16777216(16M)
{% elif (ansible_memtotal_mb | int)   <= 4096 %}
innodb_log_buffer_size              =256M                           # 16777216(16M)
{% elif (ansible_memtotal_mb | int)   <= 8192 %}
innodb_log_buffer_size              =512M                           # 16777216(16M)
{% else %}
innodb_log_buffer_size              =1G                             # 16777216(16M)
{% endif %}
innodb_redo_log_encrypt             =OFF                            # OFF
innodb_online_alter_log_max_size    =1G                             # 134217728(128M)
innodb_undo_directory               =./                             # ./
innodb_undo_log_encrypt             =OFF                            # OFF
innodb_undo_log_truncate            =ON                             # ON
innodb_max_undo_log_size            =1G                             # 1073741824(1G)
innodb_rollback_on_timeout          =OFF                            # OFF
innodb_rollback_segments            =128                            # 128 [1~128]
innodb_log_checksums                =ON                             # ON
innodb_checksum_algorithm           =crc32                          # crc32
innodb_log_compressed_pages         =ON                             # ON
innodb_doublewrite                  =ON                             # ON  ! do not disable it please.
innodb_commit_concurrency           =0                              # 0
## configuring innodb  readonly
innodb_read_only                    =OFF                            # OFF 
## configuring innodb dedicated server
innodb_dedicated_server             =OFF                            # OFF ! related to mysql auto config please donot chanage
## configuring innodb buffer pool size and instances
innodb_buffer_pool_chunk_size       =128M                           # 134217728(128M)
{% if (ansible_memtotal_mb | int)     <= 512 %}
innodb_buffer_pool_size             =128M                             # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 1024 %}
innodb_buffer_pool_size             =256M                             # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 4096 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.5 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 8109 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.6 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =2                                # 1
{% elif (ansible_memtotal_mb | int)   <= 16384 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.65 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =4                                # 1
{% elif (ansible_memtotal_mb | int)   <= 32768 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.70 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =8                                # 1
{% elif (ansible_memtotal_mb | int)   <= 65536 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.75 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =16                                # 1
{% elif (ansible_memtotal_mb | int)   <= 131072 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.80 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =24                                # 1
{% else %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.85 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =32                                # 1
{% endif %}
## making the buffer pool scan resistant
innodb_old_blocks_pct               =37                             # 37
innodb_old_blocks_time              =1000                           # 1000
## configuring innodb buffer pool prefetching(read ahead)
innodb_random_read_ahead            =off                            # OFF
innodb_read_ahead_threshold         =56                             # 56
## configuring innodb buffer pool flushing
innodb_max_dirty_pages_pct_lwm      =20                             # 10
innodb_max_dirty_pages_pct          =90                             # 90
## fine-tuning innodb buffer pool flushing
innodb_flush_neighbors              =0                              # off | on (off for ssd ,on for hdd)
innodb_lru_scan_depth               =1024                           # 1024
## tuning for sharp checkpoint
innodb_adaptive_flushing            =ON                             # ON
innodb_adaptive_flushing_lwm        =10                             # 10
innodb_flushing_avg_loops           =30                             # 30(a heih value means adaptive flushing is slow)
## saving and restoring the buffer pool state
innodb_buffer_pool_dump_pct         =50                             # 50
innodb_buffer_pool_dump_at_shutdown =ON                             # ON
innodb_buffer_pool_load_at_startup  =ON                             # ON
innodb_buffer_pool_filename         =ib_buffer_pool                 # ib_buffer_pool
innodb_stats_persistent             =ON                             # ON
innodb_stats_on_metadata            =ON                             # OFF
innodb_stats_method                 =nulls_equal                    # nulls_equal
innodb_stats_auto_recalc            =ON                             # ON
innodb_stats_include_delete_marked  =ON                             # ON
innodb_stats_persistent_sample_pages=20                             # 20
innodb_stats_transient_sample_pages =8                              # 8
innodb_status_output                =OFF                            # OFF
innodb_status_output_locks          =OFF                            # OFF
innodb_buffer_pool_dump_now         =OFF                            # OFF
innodb_buffer_pool_load_abort       =OFF                            # OFF
innodb_buffer_pool_load_now         =OFF                            # OFF
## configuring thread concurrency for innodb
innodb_thread_concurrency           =0                              # 0
#? if innodb_thread_concurrency is 0, the value of innodb_thread_sleep_delay is ignored; 
#? so default the next 3 are ignored.
innodb_concurrency_tickets          =5000                           # 5000
innodb_thread_sleep_delay           =15000                          # 10000  ( 1/100 s)
innodb_adaptive_max_sleep_delay     =150000                         # 150000 (15/100 s)
## configuring the number of background innoDB i/o threads
#? if you see more than 64 × innodb_read_io_threads pending read requests in 
#? SHOW ENGINE INNODB STATUS output, you might improve performance 
#? by increasing the value of innodb_read_io_threads.
innodb_read_io_threads              =4                              # 4
innodb_write_io_threads             =4                              # 4
## using asynchronous i/o on linux
#? 1):perform read-ahead and write requests for data file pages.
#? 2):Too many I/O write requests dispatched to the operating system for parallel processing could, 
#? in some cases, result in I/O read starvation
innodb_use_native_aio               =ON                             # ON
## configuring the innodb master thread i/o rate
innodb_flush_sync                   =OFF                            # ON
#? To adhere to the limit on InnoDB background I/O activity defined by the innodb_io_capacity setting, 
#? disable innodb_flush_sync.
innodb_io_capacity                  =4000                            # 200
innodb_io_capacity_max              =20000                           # 2000
## configuring spin lock polling
innodb_spin_wait_delay              =6                              # 6
## configuring innoDB purge scheduling
innodb_purge_threads                =4                              # 4
innodb_purge_batch_size             =300                            # 300(300 undo log page)
innodb_purge_rseg_truncate_frequency=128                            # 128


## --  Lock & Wait
innodb_deadlock_detect              =ON                             # ON
innodb_autoinc_lock_mode            =2                              # 0 | 1 | 2
innodb_print_all_deadlocks          =ON                             # OFF
innodb_lock_wait_timeout            =50                             # 50
innodb_table_locks                  =ON                             # ON
innodb_sync_array_size              =1                              # 1
innodb_sync_spin_loops              =30                             # 30 


## innodb others
innodb_print_ddl_logs               =OFF                            # OFF
innodb_replication_delay            =0                              # 0
innodb_cmp_per_index_enabled        =OFF                            # ! do not enable it please.
innodb_disable_sort_file_cache      =OFF                            # OFF
innodb_numa_interleave              =OFF                            # OFF
innodb_strict_mode                  =ON                             # ON
innodb_sort_buffer_size             =1M                             # 1M(global and only for full-text search)
innodb_fast_shutdown                =1                              # 0 | 1 | 2
innodb_force_load_corrupted         =OFF                            # OFF
innodb_force_recovery               =0                              # 0 | 1 | 2 | 3 | 4 | 5 | 6
innodb_temp_tablespaces_dir         =./#innodb_temp/                # ./#innodb_temp/
innodb_tmpdir                       =./                             # ! the sort file temp dir of alter table opration
innodb_temp_data_file_path          =ibtmp1:64M:autoextend          # ibtmp1:12M:autoextend ! stores rollback segments for changes made to user-created temporary tables.
innodb_page_cleaners                =4                              # 1


## adaptive hash index 
innodb_adaptive_hash_index          =ON                             # ON
innodb_adaptive_hash_index_parts    =8                              # 8


## -- Flush & Io
innodb_flush_log_at_timeout         =1                              # 1
innodb_flush_log_at_trx_commit      =1                              # 1 | 0 | 2
innodb_flush_method                 =O_DIRECT                       # fsync | o_direct
innodb_fsync_threshold              =0                              # 0 ~ 2**64-1
innodb_change_buffer_max_size       =25                             # 25
innodb_change_buffering             =all                            # all | none | inserts | deletes | changes | purges

## -- Clone Plugin
plugin-load-add                     =mysql_clone.so
clone                               =FORCE_PLUS_PERMANENT

####: others
div_precision_increment             =4                              # 4
eq_range_index_dive_limit           =200                            # 200
explicit_defaults_for_timestamp     =ON                             # ON
group_concat_max_len                =1024                           # 1024
flush                               =OFF                            # OFF
flush_time                          =0                              # 0
automatic_sp_privileges             =ON                             # ON
innodb_fill_factor                  =90                             # 100
innodb_file_per_table               =ON                             # ON
innodb_autoextend_increment         =64                             # 64
innodb_open_files                   =64000                          # 4000


####: for authentication
caching_sha2_password_auto_generate_rsa_keys  =ON                            # ON
caching_sha2_password_private_key_path        =private_key.pem               # private_key.pem
caching_sha2_password_public_key_path         =public_key.pem                # public_key.pem
default_authentication_plugin                 =caching_sha2_password         # caching_sha2_password
default_password_lifetime                     =0                             # 0
disconnect_on_expired_password                =ON                            # ON


####: for character
character_set_server                =utf8mb4                        # utf8mb4
collation_server                    =utf8mb4_0900_ai_ci             # utf8mb4_0900_ai_ci


####  for performance_schema
performance_schema                                                      =on    #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'


# -- ~ _ ~    ~ _ ~     ~ _ ~ -- 
# base on mysql-8.0.14 
# generated by https://www.sqlpy.com 2019年5月17日 16:13
# wechat: jianglegege
# email: 1721900707@qq.com
# -- ~ _ ~ -- 
